/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package autodiarywebserver;


import autodiarywebserver.GoogleApiDataTypes.Address;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.*;
import java.util.Iterator;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author kkkkkkkkkkkk
 */
public class FileUploadServlet extends HttpServlet{
    private static final int ROWS_NUMBER_LIMIT = 10;
    private static final String PRIMITIVE_ACTIVITIES_TABLE_NAME = "primActivities";
    private static final String GPS_DATA_TABLE_NAME = "gpsData";
    private static final String ACCELEROMETER_DATA_TABLE_NAME = "acceData";
    private static final int MAX_PLACE_QUERY_TIMES = 6;
    
    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
        processRequest(request, response);
    }

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
        processRequest(request, response);
    }

    public void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws IOException, ServletException
    {
        Connection connection = null;
        String sqlCommand = "";
        String strResponse = "";
        
        int count = 1;
        String strRow = "";
        String tableName = "";
        
        try{
            String driverName = "com.mysql.jdbc.Driver";
            String dbUser = "thang";
            String dbPassword = "123";
            String dbName = "mysql";
            String host = "localhost";
            String url = "jdbc:mysql://" + host + "/" + dbName + "?user=" 
                    + dbUser + "&password=" + dbPassword;
            Class.forName(driverName).newInstance();
            connection = DriverManager.getConnection(url);
            Statement statement = connection.createStatement();
            
            tableName = request.getParameter("tableName");

            if (tableName.equalsIgnoreCase(ACCELEROMETER_DATA_TABLE_NAME)) {
                while (count <= ROWS_NUMBER_LIMIT){
                    // get request infor
                    strRow = request.getParameter("row" + count);

                    // check null and trim.
                    if ( strRow == null ){break;}
                    strRow = strRow.trim();
                    if ( strRow.length() == 0){break;}

                    String[] arrRow = strRow.split("\\s");

                    sqlCommand = "insert into d_acceData (acceX, acceY, acceZ, sensorTimeStamp, cpuTimeStamp) "
                            + "values(" + arrRow[0] + ", " + arrRow[1] + ", " + arrRow[2] + ", " 
                            + arrRow[3] + ", " + arrRow[4] + ");";

                    statement.executeUpdate(sqlCommand);
                    count++;
                }
            }

            if (tableName.equalsIgnoreCase(PRIMITIVE_ACTIVITIES_TABLE_NAME)) {
                int time_int = 0;
                while (count <= ROWS_NUMBER_LIMIT){
                    // get request infor
                    strRow = request.getParameter("row" + count);

                    // check null and trim.
                    if ( strRow == null ){break;}
                    strRow = strRow.trim();
                    if ( strRow.length() == 0){break;}

                    String[] arrRow = strRow.split("\\s");
                    // convert time from millisecond to second
                    // from long to int
                    // this is correct for next 94 years.
                    time_int = Integer.parseInt(arrRow[1].trim().substring(0, 10));

                    sqlCommand = "insert into d_primActivities (code, time, time_int) "
                                + "values('" + arrRow[0] + "', " + arrRow[1] + ", " + time_int + ");";

                    statement.executeUpdate(sqlCommand);
                    count++;
                }
            }
            
            if (tableName.equalsIgnoreCase(GPS_DATA_TABLE_NAME)) {
                ResultSet rs = null;
                long newid = 0;
                int accuracy_int = 0;
                int speed_int = 0;
                int time_int = 0;
                
                while (count <= ROWS_NUMBER_LIMIT){
                    // get request infor
                    strRow = request.getParameter("row" + count);

                    // check null and trim.
                    if ( strRow == null ){break;}
                    strRow = strRow.trim();
                    if ( strRow.length() == 0){break;}
                    
                    // get id to insert places aswell
                    rs = statement.executeQuery("select max(id) as max_id from d_gpsData;");
                    if (rs.first()){
                        newid = rs.getLong(1) + 1;
                    }

                    String[] arrRow = strRow.split("\\s");
                    
                    accuracy_int = Math.round( Float.parseFloat( arrRow[2].trim() ) );
                    speed_int = Math.round( Float.parseFloat( arrRow[3].trim() ) );
                    time_int = Integer.parseInt(arrRow[4].trim().substring(0, 10));

                    sqlCommand = "insert into d_gpsData (id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int) "
                                + "values(" + newid + ", " + arrRow[0] + ", " + arrRow[1] + ", " + arrRow[2] + ", " 
                                + arrRow[3] + ", " + arrRow[4] + ", " + accuracy_int + ", " + speed_int + ", " + time_int + ");";

                    // insert gps_data into table
                    statement.executeUpdate(sqlCommand);
                    
                    // get gps_address and insert gps_address into table
                    GoogleApiDataTypes.Address gpsDataAddress = getGpsDataAddress(Double.parseDouble(arrRow[0]), Double.parseDouble(arrRow[1]), statement, newid);
                    
                    // query places and store in db
                    // getPlaces(lat, long, radius, statement, gpsData_id)
                    getPlaces( Double.parseDouble(arrRow[0]), Double.parseDouble(arrRow[1]), Math.round((float)Float.parseFloat(arrRow[2])), statement, newid, gpsDataAddress);
                    count++;
                }
            }
            
            // inform that server get data ok
            strResponse = "OK";
            
        } catch(Throwable t){
            t.printStackTrace();
            strResponse = "FALSE";
        }
        finally{
            try{
                connection.close();
            } catch(Throwable ignored){}
        }

        //Gets hold of the outputstream which can be used to
        //send data back to the browser (or whoever called 
        // the servlet).
        PrintWriter printWriter = new PrintWriter( new OutputStreamWriter(response.getOutputStream()), true);
        
        //Tells the browser that we are sending data in HTML format
        response.setContentType("text/html");
       
        // Send a small HTML document back
        //printWriter.println("Response from File Upload Servlet sql command: " + sqlCommand + "!");
        printWriter.println(strResponse);
        
    }
    
    public GoogleApiDataTypes.Address getGpsDataAddress(double latitude, double longitude, Statement statement, long gpsData_id) throws IOException, SQLException{
        // get addres_list
        GoogleApiDataTypes.AddressList list = GpsDecoder.reverseGeocoding(latitude, longitude);

        // get the most detail one (the first one)
        GoogleApiDataTypes.Address address = null;
        if ( (list != null) && list.status.equalsIgnoreCase(GoogleApiDataTypes.Status.OK.toString()) ){
            for (Iterator<Address> it = list.results.iterator(); it.hasNext();) {
                address = it.next();
                break;
            }

            // write to db
            String sqlCommand = buildSqlCommandAddress(address, gpsData_id, -1);
            statement.executeUpdate(sqlCommand);
        }        
        return address;
    }
    
 /*
 * if gpsData_id_in < 0 -> no gpsData_id in insert command
 * if place_id_in < 0 -> no place_id in insert command
 * gpsData_id_in and place_id_in not allow to be null (<=0) at the same time (will get null returned)
 */
    
public static String buildSqlCommandAddress(GoogleApiDataTypes.Address address, long gpsData_id_in, long place_id_in){
    // must have at least of of id
    if ( (gpsData_id_in <= 0) && (place_id_in <= 0) ){
        return null;
    }
        
        String formatted_address = address.formatted_address != null ? "formatted_address, " : "" ;
        String formatted_address_v = address.formatted_address !=null ? "'" + address.formatted_address + "'" + ", ": "";
        
        String street_number  = "";
        String street_number_v = "";
        
        String route = "";
        String route_v = "";
        
        String locality = "";
        String locality_v = "";
        
        String administrative_area_level_1 = "";
        String administrative_area_level_1_v = "";
        
        String country = "";
        String country_v = "";
        
        String postal_code = "";
        String postal_code_v = "";
        
        String gpsData_id = "";
        String gpsData_id_v = "";
        
        String place_id = "";
        String place_id_v = "";
        
        GoogleApiDataTypes.Address_components address_component = null;
        
        // get every address component
        for ( Iterator<GoogleApiDataTypes.Address_components> it = address.address_components.iterator(); it.hasNext(); ){
            address_component = it.next();
            
            // check type of the component
            for ( String type : address_component.types){
                // if have street number or street address
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_number.toString()) || 
                        type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_address.toString()) ){
                    street_number = "street_number, ";
                    street_number_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }
                
                // if have route
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.route.toString()) ){
                    route = "route, ";
                    route_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }

                // if have locality
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.locality.toString()) ){
                    locality = "locality, ";
                    locality_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }

                // if have administrative_area_level_1
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.administrative_area_level_1.toString()) ){
                    administrative_area_level_1 = "administrative_area_level_1, ";
                    administrative_area_level_1_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }

                // if have country
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.country.toString()) ){
                    country = "country, ";
                    country_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }
                
                // if have postal_code
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.postal_code.toString()) ){
                    postal_code = "postal_code, ";
                    postal_code_v = "'" + address_component.short_name + "'" + ", ";
                    break;
                }
            }
        }

        String sqlCommand = "INSERT INTO d_address(";
        String columnsCommand = formatted_address + street_number + route + locality + administrative_area_level_1 + country + postal_code;
        String valuesCommand = "VALUES(";
        valuesCommand += formatted_address_v + street_number_v + route_v + locality_v + administrative_area_level_1_v + country_v + postal_code_v;
        
        
        // if have address then store
        if ( columnsCommand == null ){ return null; }
        if ( columnsCommand.trim().length() < 1 ){ return null; }
        
        // check insert gpsData_id and place_id or not
        // initial with both are null
        if ( gpsData_id_in > 0 ){
            gpsData_id = "gpsData_id";
            gpsData_id_v = "" + gpsData_id_in;

            if ( place_id_in > 0 ){
                place_id = ", place_id";
                place_id_v = ", " + place_id_in;
            }
        } else {
            if ( place_id_in > 0 ){
                place_id = "place_id";
                place_id_v = "" + place_id_in;
            }
        }
        
        columnsCommand += gpsData_id + place_id + ") ";
        valuesCommand += gpsData_id_v + place_id_v + ")";
        sqlCommand += columnsCommand + valuesCommand + ";";
        
        return sqlCommand;
    }
    
    /*
     * from gps data find places (restaurant, bar...) where the user is
     * and stored in db (mysql.places table)
     */
    public void getPlaces(double lat, double lon, int radius, Statement statement, long gpsData_id, GoogleApiDataTypes.Address gpsDataAddress) throws IOException, SQLException{
        int tryTimes = 0;
        GoogleApiDataTypes.PlacesList placesList = null;
        ResultSet rs = null;
        int mRadius = radius;
        long newPlaceId = 0;
        
        // start search place with the radius is set equal to the accuracy (it is the smallest usable radius)
        while (true){

            tryTimes++;
            
            placesList = GpsDecoder.getPlacesList(lat, lon, mRadius);
            
            // if there is at least one place returned stop query place
            if ( placesList.status.equalsIgnoreCase("OK") ){ break; }
            // if query place more than max times break
            if (tryTimes >= MAX_PLACE_QUERY_TIMES){ break; }

            // adjust the radius to get just about 5 places
            // if radius (is set to accuracy) is big -> there to many places returned, can do nothing
            // if the radius is small (higher accuracy) -> there may be no place returned -> increase the radius
            if (placesList.status.equalsIgnoreCase("ZERO_RESULTS")){
                mRadius = mRadius * 2;
            }
        }
        
        // write place list to db
        if ( (placesList != null) && (!(placesList.status.equalsIgnoreCase("ZERO_RESULTS"))) ){
            String sqlCommand = "";
            String placeTypes = "";
            long mDistance = 0;
            int typeId = 0;
            GoogleApiDataTypes.Address placeAddress;
            
            // ----------------------------------------------------------
            //sqlCommand = "insert into d_places (name, types, distance, gpsData_id, latitude, longitude)"
            //        + "values ();";
            // ----------------------------------------------------------

            for (GoogleApiDataTypes.Place place : placesList.results) {
                
                // calculate distance to Gps reading posistion
                mDistance = fDistance(lat, lon, place.geometry.location.lat, place.geometry.location.lng);
                
                // get id to insert placesTypes aswell
                // need to lock DB to insert then unlock DB !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                rs = statement.executeQuery("select max(id) as max_id from d_places;");
                if (rs.first()){
                    newPlaceId = rs.getLong(1) + 1;
                }
                rs.close();
                rs = null;

                // construct sql command
                sqlCommand = "insert into d_places (id, name, distance, gpsData_id, latitude, longitude) "
                                + "values(" + newPlaceId + ",'" + place.name + "', " + mDistance + ", " + gpsData_id + ", " 
                                + place.geometry.location.lat + ", " + place.geometry.location.lng + ");";
                
                // insert place to table
                statement.executeUpdate(sqlCommand);

                // insert into placesTypes (place types)
                for(String placeType: place.types){
                    
                    // get id from table d_pTypes
                    rs = statement.executeQuery("select id from d_pTypes where type = '" + placeType.trim() + "';");
                    if (rs.first()){
                        typeId = rs.getInt(1);
                        rs.close();
                        rs = null;
                    }
                    else{   // not found type code, create a new one
                        rs.close();
                        rs = null;
                        
                        rs = statement.executeQuery("select max(id) as max_id from d_pTypes;");
                        if (rs.first()){
                            typeId = rs.getInt(1) + 1;
                        }
                        rs.close();
                        rs = null;
                    }

                    // insert into placesTypes
                    sqlCommand = "insert into d_placesTypes (place_id, type_id) "
                                + "values(" + newPlaceId + ", " + typeId + ");";
                    statement.executeUpdate(sqlCommand);
                }
                
                // get place_address
                placeAddress = GpsDecoder.getPlaceAddress(place.reference);
                if ( matchAddress(placeAddress, gpsDataAddress) ){ // if match update address table
                    updateAddress(statement, gpsData_id, newPlaceId);
                } else{ // insert place address to table
                    sqlCommand = buildSqlCommandAddress(placeAddress, -1, newPlaceId);
                    statement.executeUpdate(sqlCommand);
                }
                
                // mactch with gps_data address
                // store place_address if not match
            }
        }
        
        // close rs
        try{ rs.close(); }
        catch(Exception e){}
    }
    
    public void updateAddress(Statement statement, long gpsData_id, long place_id) throws SQLException{
        PreparedStatement stm = statement.getConnection().prepareStatement("UPDATE d_address SET place_id=? WHERE gpsData_id=?");
        stm.setLong(1, place_id);
        stm.setLong(2, gpsData_id);
        stm.executeUpdate();
    }
    
    /*
     * match address of place and gpsData
     * inputs: place address, gpsData_id
     * output: true if place address and gpsData_id's address are the same, false otherwise
     */
    public boolean matchAddress(GoogleApiDataTypes.Address placeAddress, GoogleApiDataTypes.Address gpsDataAddress){
        boolean result = false;
        
        String[][] placeA = addressToArray(placeAddress);
        String[][] gpsDataA = addressToArray(gpsDataAddress);
        
        // formatted_address match
        if ( placeAddress.formatted_address.trim().equalsIgnoreCase(gpsDataAddress.formatted_address.trim()) ){
            return true;
        }
        
        //street_number
        boolean bStreet_number = false;
        if ( (placeA[1][1] != null) &&  (placeA[1][1].length() > 0) ){
             bStreet_number = placeA[1][1].equalsIgnoreCase(gpsDataA[1][1]);
        }

        //route
        boolean bRoute = false;
        if ( (placeA[1][2] != null) &&  (placeA[1][2].length() > 0) ){
            // just compare first word
            String placeWord = placeA[1][2].substring( 0, placeA[1][2].indexOf(" ") ).trim();
            String gpsDataWord = "";

            if ( (gpsDataA[1][2] != null) && gpsDataA[1][2].length() > 0 ) {
                gpsDataWord = gpsDataA[1][2].substring( 0, gpsDataA[1][2].indexOf(" ") ).trim();
            }

            bRoute = placeWord.equalsIgnoreCase(gpsDataWord);
            //bRoute = placeA[1][2].equalsIgnoreCase(gpsDataA[1][2]);
        }
        
        //locality
        boolean bLocality = false;
        if ( (placeA[1][3] != null) &&  (placeA[1][3].length() > 0) ){
            bLocality = placeA[1][3].equalsIgnoreCase(gpsDataA[1][3]);
        }
        
        //Admin level1
        boolean bAdminL1 = false;
        if ( (placeA[1][4] != null) &&  (placeA[1][4].length() > 0) ){
            bAdminL1 = placeA[1][4].equalsIgnoreCase(gpsDataA[1][4]);
        }
        
        //country
        boolean bCountry = false;
        if ( (placeA[1][5] != null) &&  (placeA[1][5].length() > 0) ){
            bCountry = placeA[1][5].equalsIgnoreCase(gpsDataA[1][5]);
        }
        
        //postal_code
        boolean bPostal_Code = false;
        if ( (placeA[1][6] != null) &&  (placeA[1][6].length() > 0) ){
            bPostal_Code = placeA[1][6].equalsIgnoreCase(gpsDataA[1][6]);
        }
        
        if ( bStreet_number && bRoute && bLocality && bAdminL1 && bCountry ){
            return true;
        }
        if ( bStreet_number && bRoute && bPostal_Code && bCountry ){
            return true;
        }
        
        return result;
    }
    
    public String[][] addressToArray(GoogleApiDataTypes.Address address){
        
        // in order 
        //  0,1,2,3,4,5,6
        //formatted_address 0, street_number 1, route 2, locality 3, administrative_area_level_1 4, country 5, postal_code 6
        String[][] result = new String[2][7];
        
        // init array
        for( int i=0; i < result.length; i++ ){
            for( int j=0; j<result[i].length; j++ ){
                result[i][j] = "";
            }
        }
        
        result[0][0] = "formatted_address";
        result[1][0] = address.formatted_address;
        
        GoogleApiDataTypes.Address_components address_component = null;
        
        for ( Iterator<GoogleApiDataTypes.Address_components> it = address.address_components.iterator(); it.hasNext(); ){
            address_component = it.next();
            
            // check type of the component
            for ( String type : address_component.types){
                // if have street number or street address
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_number.toString()) || 
                        type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_address.toString()) ){
                    result[0][1] = GoogleApiDataTypes.Address_Component_Types.street_number.toString();
                    result[1][1] = address_component.short_name.trim();
                    break;
                }
                
                // if have route
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.route.toString()) ){
                    result[0][2] = GoogleApiDataTypes.Address_Component_Types.route.toString();
                    result[1][2] = address_component.short_name.trim();
                    break;
                }

                // if have locality
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.locality.toString()) ){
                    result[0][3] = GoogleApiDataTypes.Address_Component_Types.locality.toString();
                    result[1][3] = address_component.short_name.trim();
                    break;
                }

                // if have administrative_area_level_1
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.administrative_area_level_1.toString()) ){
                    result[0][4] = GoogleApiDataTypes.Address_Component_Types.administrative_area_level_1.toString();
                    result[1][4] = address_component.short_name.trim();
                    break;
                }

                // if have country
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.country.toString()) ){
                    result[0][5] = GoogleApiDataTypes.Address_Component_Types.country.toString();
                    result[1][5] = address_component.short_name.trim();
                    break;
                }
                
                // if have postal_code
                if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.postal_code.toString()) ){
                    result[0][6] = GoogleApiDataTypes.Address_Component_Types.postal_code.toString();
                    result[1][6] = address_component.short_name.trim();
                    break;
                }
            }
        }
        
        return result;
    }
    
    /*
     * calculate distace between two points specified by lat and long
     * ref: using haversine formula from: http://www.movable-type.co.uk/scripts/latlong.html
     * return: distance in m
     */
    public static long fDistance(double lat1, double lng1, double lat2, double lng2){
        int R = 6371;   //km
        double dLat = Math.toRadians(lat2 - lat1);
        double dLng = Math.toRadians(lng2 - lng1);
        //double dLat = (lat2 - lat1) * (Math.PI / 180);
        //double dLng = (lng2 - lng1) * (Math.PI / 180);
        double rLat1 = Math.toRadians(lat1);
        double rLat2 = Math.toRadians(lat2);
        
        double a = Math.pow(Math.sin(dLat/2),2) + Math.cos(rLat1) * Math.cos(rLat2) * Math.pow(Math.sin(dLng/2), 2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double d = R * c;
        
        return Math.round(d*1000);
    }

}
